package com.edu.DAO;
import java.util.List;
import com.edu.model.Shoes;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.*;
import com.edu.Utils.DataSourceUtils;


public class PageListDAO {
	QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
	//查询商品总数
    public int getshoesnum() {
	int num=0;
	String sql="select count(*) from goods where flag=1";
	try{
		 num=(int)qr.query(sql, new ScalarHandler());
	}
	catch (SQLException e) {
		e.printStackTrace();
	}
	return num;
	}
    //无条件列表查询
    public List<Shoes> qruerylistall(int start,int listnum){
    	
    	List<Shoes> sh=null;
    	String sql="select * from goods limit " + start + "," + listnum;
    	try {
    		sh=qr.query(sql, new BeanListHandler<>(Shoes.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return sh;
    } 
    //按照男女鞋查询
    public List<Shoes> qruerylistbysextype(int start,int listnum,String sextype){
    	List<Shoes> sh=null;
    	String sql="select * from goods where (c_sexytpe=? or c_sexytpe='通用') and flag=1 limit " + start + "," + listnum;
    	try {
    		sh=qr.query(sql, new BeanListHandler<>(Shoes.class),sextype);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return sh;
    } 
    //男，女鞋二级分类查询
    public List<Shoes> qruerylistbysexclass2(int start,int listnum,String sextype,String cname) {
    	List<Shoes> sh=null;
    	String sql="select * from goods where (c_sexytpe=? or c_sexytpe='通用') and c_name=? limit " + start + "," + listnum;
    	try {
    		sh=qr.query(sql, new BeanListHandler<>(Shoes.class),sextype,cname);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return sh;
    } 

    //男,女鞋一级分类查询
    public List<Shoes> qruerylistbysexclass1(int start,int listnum,String sextype,String cname){
    	List<Shoes> sh=null;
    	String sql="select * from goods where c_id IN ( select c_id from class where  p_name=? ) and sextype=? and flag=1 limit " + start + "," + listnum;
    	try {
    		sh=qr.query(sql, new BeanListHandler<>(Shoes.class),cname,sextype);
		} catch (SQLException e) {
			e.printStackTrace();
		}
    	return sh;
    }
    //男，女鞋热卖
    //男，女鞋品牌
}
